<?php

include "DBconn.php";

class SalesConformationMaster {

	public function getItemsForDo($customerFk) {
        $ret = array();
        $query = "Select sh.serialnumber,sh.sales_con_code,sd.detserialnumber,sd.pro_serialnumber_fk,sd.pro_qty,sd.warehouse_serialnumber_fk, 
		pro.pro_name,pro.part_number,pro.pro_description from sales_conformation_header as sh LEFT JOIN sales_conformation_details as sd ON sd.activestatus=1 and 
		sd.pro_status!='FULLY SATISFIED' and sd.pro_status!='VOID' and sd.sc_serialnumber_fk=sh.serialnumber LEFT JOIN product_header as pro ON pro.activestatus=1 and 
		pro.serialnumber=sd.pro_serialnumber_fk where sh.activestatus=1 and sh.sales_con_status!='DRAFT' and sh.sales_con_status!='FULLY SATISFIED' and 
		sh.customer_serialnumber_fk='$customerFk' ORDER BY sh.serialnumber";
        $s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) 
		{       
			$balance_to_sale = 0;
			$po_detserialnumberArr = array();
			
			$query2 = "Select pms.map_serialnumber,pms.po_detserialnumber,pms.sc_detserialnumber,IFNULL(Sum(pms.allocated_qty-(Select IFNULL(Sum(gmd.do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 and gmd.po_detserialnumber_fk=pms.po_detserialnumber and gmd.sc_detserialnumber_fk=pms.sc_detserialnumber)),0) as balance_qty,(Select IFNULL(Sum(gmd.do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 and gmd.po_detserialnumber_fk=pms.po_detserialnumber and gmd.sc_detserialnumber_fk=pms.sc_detserialnumber) as delivered_qty from po_map_sc as pms LEFT JOIN po_details as pd ON pd.activestatus=1 and pd.po_detserialnumber=pms.po_detserialnumber where pms.activestatus=1 and pms.sc_detserialnumber='$row->detserialnumber' GROUP BY pms.po_detserialnumber HAVING balance_qty>0 ORDER BY pms.map_serialnumber";
			$s2 = mysql_query($query2) or die(mysql_error());
			while ($row2 = mysql_fetch_object($s2)) 
			{
				$reserved_qty = 0;
				
				$query3 = "Select (IFNULL((Select IFNULL(Sum(gd.received_qty-(IFNULL((Select IFNULL(Sum(gmd.do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 and gmd.grn_detserialnumber_fk=gd.grn_detserialnumber),0))),0) from grn_detail as gd where gd.activestatus=1 and gd.po_detserialnumber_fk=smp.po_detserialnumber),0) -  IFNULL((Select IFNULL(Sum(pms.allocated_qty-(IFNULL((Select IFNULL(Sum(gmd.do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 and gmd.sc_detserialnumber_fk=pms.sc_detserialnumber and gmd.po_detserialnumber_fk=pms.po_detserialnumber),0))),0) from po_map_sc as pms where pms.activestatus=1 and pms.po_detserialnumber=smp.po_detserialnumber and pms.self_sc=0 and pms.map_serialnumber<smp.map_serialnumber and pms.sc_detserialnumber IN(Select spms.sc_detserialnumber from po_map_sc as spms where spms.po_detserialnumber=smp.po_detserialnumber and spms.activestatus=1 and spms.self_sc=0 and (spms.sc_type=1 or spms.sc_type=3 or(spms.sc_type=2 and spms.map_serialnumber < (Select map_serialnumber from po_map_sc where activestatus=1 
				and self_sc=0 and sc_type=1 and po_detserialnumber=smp.po_detserialnumber and map_serialnumber>spms.map_serialnumber and map_serialnumber<=smp.map_serialnumber LIMIT 1))) and spms.map_serialnumber<smp.map_serialnumber)),0)) as on_stock,IFNULL((Select IFNULL(Sum(psc.allocated_qty-IFNULL((Select IFNULL(Sum(sgmd.do_pro_qty),0) from gr_do as sgmd where sgmd.activestatus=1 and sgmd.sc_detserialnumber_fk=smp.sc_detserialnumber and sgmd.po_detserialnumber_fk=smp.po_detserialnumber),0)),0) from po_map_sc as psc where psc.activestatus=1 and psc.sc_detserialnumber=smp.sc_detserialnumber and psc.po_detserialnumber=smp.po_detserialnumber),0) as balance_to_deliver from po_map_sc as smp where smp.activestatus=1 and smp.sc_detserialnumber='$row2->sc_detserialnumber' and smp.po_detserialnumber='$row2->po_detserialnumber' GROUP BY smp.sc_detserialnumber HAVING on_stock > 0 LIMIT 1";
				$s3 = mysql_query($query3) or die(mysql_error());
				if($row3 = mysql_fetch_object($s3))
				{
					$on_stock = (int)$row3->on_stock;
					$balance_to_deliver = (int)$row3->balance_to_deliver;
			
					if($balance_to_deliver <= $on_stock)
					{
						$reserved_qty = $balance_to_deliver;
					}else{$reserved_qty = $on_stock;}
					
					$po_detserialnumberArr[] = (int)$row2->po_detserialnumber;
				}			
			  $balance_to_sale = $balance_to_sale + $reserved_qty;
			}			
			
			if($balance_to_sale > 0)
			{
				$temp = new VoScSatisfied();
				$temp->po_detserialnumber = $po_detserialnumberArr;			
				$temp->sc_serialnumber = (int) $row->serialnumber;
				$temp->sales_con_code = $row->sales_con_code;
				$temp->sc_detserialnumber = (int) $row->detserialnumber;			
				$temp->pro_serialnumber_fk = (int) $row->pro_serialnumber_fk;
				$temp->pro_name =  $row->pro_name;
				$temp->part_number =  $row->part_number;
				$temp->pro_description =  $row->pro_description;	
				$temp->sc_qty = (int) $row->pro_qty;
				$temp->reserved_qty = $balance_to_sale;
				$temp->isSelected = false;
				$ret[] = $temp;			
			}			
        }
        return $ret;
    }
	
	
	public function addNewSalesConformation($VObject) 
	{
		$sales_con_code = "";
	
		 if ($VObject->sales_con_status != "DRAFT") 
		{	 
			if(mysql_real_escape_string($VObject->sales_type[0])=="Ex-Stock Sales" && $VObject->customer_serialnumber_fk!=0)
			{
				$sales_con_code = $this->generateScExstockCode();
				mysql_query("insert into sc_exstock_code(exstock_code) values ('$sales_con_code')") or die(mysql_error());
				
			}else if(mysql_real_escape_string($VObject->sales_type[0])=="Advanced Sales" && $VObject->customer_serialnumber_fk!=0)
			{
				$sales_con_code = $this->generateScAdvancedCode();
				mysql_query("insert into sc_advanced_code(advanced_code) values ('$sales_con_code')") or die(mysql_error());
				
			}else if(mysql_real_escape_string($VObject->sales_type[0])=="Indent Sales" && $VObject->customer_serialnumber_fk!=0)
			{
				$sales_con_code = $this->generateScIndentCode();
				mysql_query("insert into sc_indent_code(indent_code) values ('$sales_con_code')") or die(mysql_error());
			}else if($VObject->customer_serialnumber_fk==0)
			{
				$sales_con_code = $this->generateScSelfCode();
				mysql_query("insert into sc_self_code(self_code) values ('$sales_con_code')") or die(mysql_error());
			}
        }else {
            $sales_con_code = "Draft";
        }
		
		$return_mess = $sales_con_code;
		
        $query = "insert into sales_conformation_header(sales_con_code,customer_serialnumber_fk,total_amount,total_qty,sc_term,sc_gst,currency_serialnumber_fk,current_rate,sales_con_status,sales_con_date,no_of_days,expected_datetime,sc_remarks,cre_userid_fk,cre_datetime,activestatus) VALUES ('$sales_con_code','$VObject->customer_serialnumber_fk','$VObject->total_amount','$VObject->total_qty','$VObject->sc_term','$VObject->sc_gst','$VObject->currency_serialnumber_fk','$VObject->current_rate','$VObject->sales_con_status','$VObject->sales_con_date','$VObject->no_of_days','$VObject->expected_datetime','$VObject->sc_remarks','$VObject->cre_userid_fk','$VObject->cre_datetime',1)";
        $s = mysql_query($query) or die(mysql_error());
        if ($s) {
            $last_id = mysql_insert_id();
			foreach ($VObject->quot_detserialnumber_fk as $r => $value) 
			{
                $query1 = "insert into quot_det_sc(quot_detserialnumber_fk,sc_serialnumber_fk,cre_userid_fk,cre_datetime,activestatus) values('$value','$last_id','$VObject->cre_userid_fk','$VObject->cre_datetime',1)";
                mysql_query($query1) or die(mysql_error());
            }
            foreach ($VObject->pro_serialnumber_fk as $r => $value) 
			{
				$pro_detserialnumber = mysql_real_escape_string($VObject->pro_detserialnumber[$r]);
				$base_currency_detserialnumber_fk = mysql_real_escape_string($VObject->base_currency_detserialnumber_fk[$r]);
                $price_per_unit = mysql_real_escape_string($VObject->price_per_unit[$r]);
                $pro_qty = mysql_real_escape_string($VObject->pro_qty[$r]);
                $pro_total_price = mysql_real_escape_string($VObject->pro_total_price[$r]);
                $pro_status = mysql_real_escape_string($VObject->pro_status[$r]);
                $delivery_loc = mysql_real_escape_string($VObject->delivery_loc[$r]);
                $sales_type = mysql_real_escape_string($VObject->sales_type[$r]);
                $warehouse_serialnumber_fk = 0;//mysql_real_escape_string($VObject->warehouse_serialnumber_fk[$r]);
                $query2 = "insert into sales_conformation_details(sc_serialnumber_fk,pro_serialnumber_fk,pro_detserialnumber,base_currency_detserialnumber_fk,pro_qty,price_per_unit,pro_total_price,pro_status,delivery_loc,warehouse_serialnumber_fk,sales_type,cre_userid_fk,cre_datetime,activestatus) values('$last_id','$value','$pro_detserialnumber','$base_currency_detserialnumber_fk','$pro_qty','$price_per_unit','$pro_total_price','$pro_status','$delivery_loc','$warehouse_serialnumber_fk','$sales_type','$VObject->cre_userid_fk','$VObject->cre_datetime',1)";
                mysql_query($query2) or die(mysql_error());
				$lastDetId = mysql_insert_id();
				
				if($sales_type == "Ex-Stock Sales")
				{
				  $this->mapExstockScWithPO($lastDetId,$value,$pro_qty,$VObject->cre_userid_fk,$VObject->cre_datetime);
				}else if($sales_type == "Advanced Sales")
				{
                  $this->mapAdvancedScWithPO($lastDetId,$value,$pro_qty,$VObject->cre_userid_fk,$VObject->cre_datetime);
				}
            }
        }

       return $return_mess;
    }
	
	public function mapAdvancedScWithPO($scDetserial,$pro_serialnumber,$quantity,$cre_userid_fk,$cre_datetime)
	{
	   $balance_qty = $quantity;
	   
	   while($balance_qty > 0)
	   {
			$query1  = "Select pd.po_detserialnumber,pd.pro_qty,pd.warehouse_serialnumber_fk,IFNULL((Select IFNULL(Sum(pms.allocated_qty),0) from po_map_sc as pms where pms.activestatus=1 and pms.self_sc=1 and pms.po_detserialnumber=pd.po_detserialnumber GROUP BY pms.po_detserialnumber),0) - (GREATEST((IFNULL((Select IFNULL(Sum(gd.received_qty),0) 
			from grn_detail as gd where gd.activestatus=1 and gd.po_detserialnumber_fk=pd.po_detserialnumber GROUP BY gd.po_detserialnumber_fk),0) - IFNULL((Select IFNULL(Sum(pms.allocated_qty),0) from po_map_sc as pms where pms.activestatus=1 and pms.self_sc=0 and pms.po_detserialnumber=pd.po_detserialnumber GROUP BY pms.po_detserialnumber),0)),0)) as free_allocated_qty 
			from po_details as pd where pd.activestatus=1 and pd.pro_serialnumber_fk='$pro_serialnumber' HAVING free_allocated_qty >0 ORDER BY pd.po_detserialnumber ASC LIMIT 1";
			$result1 = mysql_query($query1) or die(mysql_error());
			$row1 = mysql_fetch_object($result1);
		
			$free_allocated_qty = (int)$row1->free_allocated_qty;
			$po_detserialnumber = (int)$row1->po_detserialnumber;
			$warehouse_serialnumber_fk = (int)$row1->warehouse_serialnumber_fk;
		 
			//echo "free_allocated_qty".$free_allocated_qty."</br>";
		 
			//Update Stock Status
			$query3 = "Select free_for_allocation from stock_header where pro_serialnumber='$pro_serialnumber' and warehouse_serialnumber_fk='$warehouse_serialnumber_fk' and activestatus=1";
			$result3 = mysql_query($query3) or die(mysql_error());
			$row3 = mysql_fetch_object($result3);
			
			$free_for_allocation = (int) $row3->free_for_allocation;
		
			//echo "1free_for_allocation".$free_for_allocation."</br>"; 
			
			$allocated_qty = 0;
			
			$free_allocation_det_qty = 0;
		
			$pre_free_allocation_qty = $free_for_allocation;
			
			if($balance_qty >= $free_allocated_qty) 
			{
				$balance_qty = $balance_qty - $free_allocated_qty; 
				$allocated_qty = $free_allocated_qty;
				$free_for_allocation = $free_for_allocation - $free_allocated_qty;
				$free_allocated_qty = 0;
			}else if($balance_qty < $free_allocated_qty)
			{
				$free_allocated_qty = $free_allocated_qty - $balance_qty;
				$allocated_qty = $balance_qty;
				$free_for_allocation = $free_for_allocation - $balance_qty;
				$balance_qty = 0;
			}
			
			$post_free_allocation_qty = $free_for_allocation;
			$free_allocation_det_qty = $pre_free_allocation_qty - $post_free_allocation_qty;
			
			//echo "2free_for_allocation".$free_for_allocation."</br>"; 
			
			//echo "3balance_qty".$balance_qty; 
			
			$query2 = "Insert into po_map_sc(po_detserialnumber,sc_detserialnumber,allocated_qty,sc_type,self_sc,cre_userid_fk,cre_datetime,activestatus) values('$po_detserialnumber','$scDetserial','$allocated_qty',2,0,'$cre_userid_fk','$cre_datetime',1)";
			mysql_query($query2) or die(mysql_error());
			
			$this->updateSelfScAndProductStatus($po_detserialnumber,$allocated_qty,$cre_userid_fk,$cre_datetime);
		
			$query4 = "update stock_header set free_for_allocation='$free_for_allocation' where pro_serialnumber='$pro_serialnumber' and warehouse_serialnumber_fk='$warehouse_serialnumber_fk' and activestatus=1";
			mysql_query($query4) or die(mysql_error());

			$query5 = "Insert Into stock_details(warehouse_serialnumber_fk,trans_detserialnumber,trans_type,pro_serialnumber,on_hand,on_sale,in_order,sold,free_for_allocation,activestatus) values('$warehouse_serialnumber_fk','$scDetserial',3,'$pro_serialnumber',0,0,0,0,'$free_allocation_det_qty',1)";
			mysql_query($query5) or die(mysql_error());
		}

	}
	
	public function mapExstockScWithPO($scDetserial,$pro_serialnumber,$quantity,$cre_userid_fk,$cre_datetime)
	{
	   $balance_qty = $quantity;
	   
	   while($balance_qty > 0)
	   {
		  $query1  = "Select pd.po_detserialnumber,pd.pro_qty,pd.warehouse_serialnumber_fk,IFNULL((Select IFNULL(Sum(gd.received_qty),0) from grn_detail as gd where gd.activestatus=1 and gd.po_detserialnumber_fk=pd.po_detserialnumber GROUP BY gd.po_detserialnumber_fk),0) - IFNULL((Select IFNULL(Sum(pms.allocated_qty),0) from po_map_sc as pms where pms.activestatus=1 and pms.self_sc=0 and pms.po_detserialnumber=pd.po_detserialnumber GROUP BY pms.po_detserialnumber),0) as exstock_qty 
		  from po_details as pd where pd.activestatus=1 and pd.pro_serialnumber_fk='$pro_serialnumber' HAVING exstock_qty>0 ORDER BY pd.po_detserialnumber ASC LIMIT 1";
		  $result1 = mysql_query($query1) or die(mysql_error());
	      $row1 = mysql_fetch_object($result1);
			
			$exstock_qty = (int)$row1->exstock_qty;
			$po_detserialnumber = (int)$row1->po_detserialnumber;
			$warehouse_serialnumber_fk = (int)$row1->warehouse_serialnumber_fk;
			
			//Update Stock Status
			$query3 ="Select on_sale,on_hand from stock_header where pro_serialnumber='$pro_serialnumber' and warehouse_serialnumber_fk='$warehouse_serialnumber_fk' and activestatus=1";
			$result3 = mysql_query($query3) or die(mysql_error());
			$row3 = mysql_fetch_object($result3); 			
		
			$on_sale = (int) $row3->on_sale;
			$on_hand = (int) $row3->on_hand;
		
			$pre_on_hand_qty = $on_hand;
			$pre_on_sale_qty = $on_sale;
		
			$allocated_qty = 0;
		
			$on_hand_det_qty = 0;
			$on_sale_det_qty = 0;
		
			if($balance_qty >= $exstock_qty) 
			{
				$balance_qty = $balance_qty - $exstock_qty; 
				$allocated_qty = $exstock_qty;
				$on_hand = $on_hand - $exstock_qty;
				$on_sale = $on_sale + $exstock_qty;
				$exstock_qty = 0;
			}else if($balance_qty < $exstock_qty)
			{
				$exstock_qty = $exstock_qty - $balance_qty;
				$allocated_qty = $balance_qty;
				$on_hand = $on_hand - $balance_qty;
				$on_sale = $on_sale + $balance_qty;
				$balance_qty = 0;
			}
		
			$post_on_hand_qty = $on_hand;
			$on_hand_det_qty = $pre_on_hand_qty - $post_on_hand_qty;
			
			$post_on_sale_qty = $on_sale;
			$on_sale_det_qty = $post_on_sale_qty - $pre_on_sale_qty;
			
			$query2 = "insert into po_map_sc(po_detserialnumber,sc_detserialnumber,allocated_qty,sc_type,self_sc,cre_userid_fk,cre_datetime,activestatus) values('$po_detserialnumber','$scDetserial','$allocated_qty',1,0,'$cre_userid_fk','$cre_datetime',1)";
			mysql_query($query2) or die(mysql_error());
			
			$this->updateSelfScAndProductStatus($po_detserialnumber,$allocated_qty,$cre_userid_fk,$cre_datetime);
			
			$query4 = "update stock_header set on_sale='$on_sale',on_hand='$on_hand' where pro_serialnumber='$pro_serialnumber' 
			and warehouse_serialnumber_fk='$warehouse_serialnumber_fk' and activestatus=1";
			$result4 = mysql_query($query4) or die(mysql_error());
		
			$query5 = "Insert Into stock_details(warehouse_serialnumber_fk,trans_detserialnumber,trans_type,pro_serialnumber,on_hand,on_sale,in_order,sold,free_for_allocation,activestatus) values('$warehouse_serialnumber_fk','$scDetserial',3,'$pro_serialnumber','$on_hand_det_qty','$on_sale_det_qty','0','0','0',1)";
			$s5 = mysql_query($query5) or die(mysql_error());
	   }
	}
	
	public function updateSelfScAndProductStatus($po_detserialnumber,$allocated_qty,$cre_userid_fk,$cre_datetime)
	{		
		$query1 = "Select * from po_map_sc where po_detserialnumber='$po_detserialnumber' and self_sc=1 and activestatus=1";
		$result1  = mysql_query($query1) or die(mysql_error());
		while($row1 = mysql_fetch_object($result1)){
		
		 if($allocated_qty > 0) {
			
			$map_serialnumber = (int)$row1->map_serialnumber;
			$self_sc_det = (int)$row1->sc_detserialnumber;
			$free_pro_qty = (int)$row1->allocated_qty;
			
			$map_pro_qty = 0;
			//Map Table Quantity
			if($free_pro_qty > $allocated_qty){
				$map_pro_qty = $allocated_qty;
				$allocated_qty = 0;
			}else{
				$map_pro_qty = $free_pro_qty;
				$allocated_qty = $allocated_qty - $free_pro_qty;
			}
			
			$query3 = "Select sc_serialnumber_fk from sales_conformation_details where detserialnumber='$self_sc_det' and activestatus=1";
			$result3  = mysql_query($query3) or die(mysql_error());
			$row3 = mysql_fetch_object($result3);
			$sc_serialnumber_fk = (int)$row3->sc_serialnumber_fk;
			
			//Update Product and SC Status.If its 0 then become void
		
			$query2 = "Update sales_conformation_details set pro_status=IF((pro_qty-$map_pro_qty)>0,pro_status,'VOID'),pro_qty=pro_qty-$map_pro_qty where detserialnumber='$self_sc_det' and activestatus=1";
			mysql_query($query2) or die(mysql_error());
			
			$query4 =  "Select pro_status from sales_conformation_details where sc_serialnumber_fk='$sc_serialnumber_fk' and activestatus=1 and pro_status='OPEN'";
			$result4  = mysql_query($query4) or die(mysql_error());
			$num_rows = mysql_num_rows($result4);
			if($num_rows==0)
			{
				$query5 = "Update sales_conformation_header set sales_con_status='VOID' where serialnumber='$sc_serialnumber_fk' and activestatus=1";
				mysql_query($query5) or die(mysql_error());
			}
			
			$query6 = "Update po_map_sc set activestatus=0 where map_serialnumber='$map_serialnumber'";
			mysql_query($query6) or die(mysql_error());
		
			//Self Sc Map That Got Previous Free Allocated
			$query7 = "Insert into po_map_sc(po_detserialnumber,sc_detserialnumber,allocated_qty,sc_type,self_sc,cre_userid_fk,cre_datetime,activestatus) values('$po_detserialnumber','$self_sc_det','".($free_pro_qty - $map_pro_qty)."',0,1,'$cre_userid_fk','$cre_datetime',1)";
			mysql_query($query7) or die(mysql_error());
		
			//Update Contents On Table
			$sc_total_price = 0;
			$sc_total_qty = 0;
			$result7 = mysql_query("Select * from sales_conformation_details where sc_serialnumber_fk='$sc_serialnumber_fk'") or die(mysql_error());
			while($row7 = mysql_fetch_object($result7))
			{
				$pro_total_price = round(($row7->price_per_unit * $row7->pro_qty),4);
				$sc_total_price = $sc_total_price +  $pro_total_price;
				$sc_total_qty = $sc_total_qty + (int)$row7->pro_qty;		   
				mysql_query("Update sales_conformation_details set pro_total_price='$pro_total_price' where detserialnumber='$row7->detserialnumber'") or die(mysql_error());
			}
			$sc_total_price = round($sc_total_price,4);
			mysql_query("Update sales_conformation_header set total_amount='$sc_total_price',total_qty='$sc_total_qty' where serialnumber='$sc_serialnumber_fk'") or die(mysql_error());
			
		}else{break;}
		
		}
	}
	
    public function addNewIndentSalesConformationWithPO($VObject,$VObjectP) 
	{	
	    $scDetserials = array();
		$sales_con_code = "";
		
		if($VObject->customer_serialnumber_fk!=0)
		{
			$sales_con_code = $this->generateScIndentCode();
			mysql_query("insert into sc_indent_code(indent_code) values ('$sales_con_code')") or die(mysql_error());
		}else
		{
			$sales_con_code = $this->generateScSelfCode();
			mysql_query("insert into sc_self_code(self_code) values ('$sales_con_code')") or die(mysql_error());
		}
		
		$return_mess = $sales_con_code;
		
        $query = "insert into sales_conformation_header(sales_con_code,customer_serialnumber_fk,total_amount,total_qty,sc_term,sc_gst,currency_serialnumber_fk,current_rate,sales_con_status,sales_con_date,no_of_days,expected_datetime,sc_remarks,cre_userid_fk,cre_datetime,activestatus) VALUES ('$sales_con_code','$VObject->customer_serialnumber_fk','$VObject->total_amount','$VObject->total_qty','$VObject->sc_term','$VObject->sc_gst','$VObject->currency_serialnumber_fk','$VObject->current_rate','$VObject->sales_con_status','$VObject->sales_con_date','$VObject->no_of_days','$VObject->expected_datetime','$VObject->sc_remarks','$VObject->cre_userid_fk','$VObject->cre_datetime',1)";
        $s = mysql_query($query) or die(mysql_error());
        if ($s) 
		{
            $last_id = mysql_insert_id();
			foreach ($VObject->quot_detserialnumber_fk as $r => $value) {
                $query1 = "insert into quot_det_sc(quot_detserialnumber_fk,sc_serialnumber_fk,cre_userid_fk,cre_datetime,activestatus) values('$value','$last_id','$VObject->cre_userid_fk','$VObject->cre_datetime',1)";
                mysql_query($query1) or die(mysql_error());
            }
            foreach ($VObject->pro_serialnumber_fk as $r => $value) 
			{
				$pro_detserialnumber = mysql_real_escape_string($VObject->pro_detserialnumber[$r]);
				$base_currency_detserialnumber_fk = mysql_real_escape_string($VObject->base_currency_detserialnumber_fk[$r]);
                $price_per_unit = mysql_real_escape_string($VObject->price_per_unit[$r]);
                $pro_qty = mysql_real_escape_string($VObject->pro_qty[$r]);
                $pro_total_price = mysql_real_escape_string($VObject->pro_total_price[$r]);
                $pro_status = mysql_real_escape_string($VObject->pro_status[$r]);
                $delivery_loc = mysql_real_escape_string($VObject->delivery_loc[$r]);
                $sales_type = mysql_real_escape_string($VObject->sales_type[$r]);
                $warehouse_serialnumber_fk = 0;//mysql_real_escape_string($VObject->warehouse_serialnumber_fk[$r]);
                $query2 = "insert into sales_conformation_details(sc_serialnumber_fk,pro_serialnumber_fk,pro_detserialnumber,base_currency_detserialnumber_fk,pro_qty,price_per_unit,pro_total_price,pro_status,delivery_loc,warehouse_serialnumber_fk,sales_type,cre_userid_fk,cre_datetime,activestatus) values('$last_id','$value','$pro_detserialnumber','$base_currency_detserialnumber_fk','$pro_qty','$price_per_unit','$pro_total_price','$pro_status','$delivery_loc','$warehouse_serialnumber_fk','$sales_type','$VObject->cre_userid_fk','$VObject->cre_datetime',1)";
                mysql_query($query2) or die(mysql_error());
				$lastDetId = mysql_insert_id();
				$scDetserials[] = $lastDetId;
            }
        }
		
		if(count($scDetserials) > 0)
		{
		   $po_id = "";
		   
		   if($VObject->customer_serialnumber_fk!=0)
		   {
				if($VObject->sales_type[0]=='Ex-Stock Sales')
				{
					$po_id = $this->addNewPoForIndentSC($VObjectP,$scDetserials,1,0);
				}else if($VObject->sales_type[0]=='Advanced Sales'){
					$po_id = $this->addNewPoForIndentSC($VObjectP,$scDetserials,2,0);
				}else if($VObject->sales_type[0]=='Indent Sales'){
					$po_id = $this->addNewPoForIndentSC($VObjectP,$scDetserials,3,0);
				}
				
		   }else{$po_id = $this->addNewPoForIndentSC($VObjectP,$scDetserials,0,1);}
		   
		   $return_mess = $return_mess." and ".$po_id;
		}
		
       return $return_mess;
    }
	
	public function addNewPoForIndentSC($VObjectP,$scDetserials,$sc_type,$self_sc)
	{
        $po_id = $this->generatePoCode();
        mysql_query("insert into po_code_table(po_code) values ('$po_id')") or die(mysql_error());
        
		$query = "insert into po_header(po_id,supplier_serialnumber_fk,total_amount,total_qty,po_term,po_gst,currency_serialnumber_fk,current_rate,po_issue_date,po_confirm_date,no_of_days,expected_datetime,po_status,po_remarks,cre_userid_fk,cre_datetime,activestatus) values('$po_id','$VObjectP->supplier_serialnumber_fk','$VObjectP->total_amount','$VObjectP->total_qty','$VObjectP->po_term','$VObjectP->po_gst','$VObjectP->currency_serialnumber_fk','$VObjectP->current_rate','$VObjectP->po_issue_date','$VObjectP->po_confirm_date','$VObjectP->no_of_days','$VObjectP->expected_datetime','$VObjectP->po_status','$VObjectP->po_remarks','$VObjectP->cre_userid_fk','$VObjectP->cre_datetime',1)";
        $s = mysql_query($query) or die(mysql_error());
        if ($s) {
            $last_id = mysql_insert_id();
			$i = 0;
            foreach ($VObjectP->pro_serialnumber_fk as $r => $value) 
			{
                $pro_qty = mysql_real_escape_string($VObjectP->pro_qty[$r]);
                $unit_price = mysql_real_escape_string($VObjectP->unit_price[$r]);
                $pro_total_price = mysql_real_escape_string($VObjectP->pro_total_price[$r]);
                $pro_status = mysql_real_escape_string($VObjectP->pro_status[$r]);
                $warehouse_serialnumber_fk = mysql_real_escape_string($VObjectP->warehouse_serialnumber_fk[$r]);
                $query2 = "insert into po_details(po_serialnumber_fk,sc_detserialnumber_fk,pro_serialnumber_fk,pro_qty,unit_price,pro_total_price,pro_status,warehouse_serialnumber_fk,cre_userid_fk,cre_datetime,activestatus) values('$last_id','$scDetserials[$i]','$value','$pro_qty','$unit_price','$pro_total_price','$pro_status','$warehouse_serialnumber_fk','$VObjectP->cre_userid_fk','$VObjectP->cre_datetime',1)";
                mysql_query($query2) or die(mysql_error());
				$po_det_last_id = mysql_insert_id();
				
				if($self_sc==0)
					{
						$query3 = "insert into po_map_sc(po_detserialnumber,sc_detserialnumber,allocated_qty,sc_type,self_sc,cre_userid_fk,
						cre_datetime,activestatus) values('$po_det_last_id','$scDetserials[$i]','$pro_qty','$sc_type','$self_sc','$VObjectP->cre_userid_fk',
						'$VObjectP->cre_datetime',1)";
						mysql_query($query3) or die(mysql_error());
						
						//Update Inorder Details For Customer
							$query4 = "select in_order from stock_header where pro_serialnumber='$value' and warehouse_serialnumber_fk='$warehouse_serialnumber_fk'";
							$result4 = mysql_query($query4) or die(mysql_error());
							while ($row4 = mysql_fetch_object($result4)) 
							{							
								$in_order = (int) $row4->in_order;
									
								$pre_inorder_qty = $in_order;
									
								$in_order = $in_order + $pro_qty;
									
								$post_inorder_qty = $in_order;
									
								$inorder_det_qty = $post_inorder_qty - $pre_inorder_qty;
								
								$query5 = "update stock_header set in_order='$in_order' where pro_serialnumber='$value' and warehouse_serialnumber_fk='$warehouse_serialnumber_fk'";
								$s1 = mysql_query($query5) or die(mysql_error());
								
								$query6 = "Insert Into stock_details(warehouse_serialnumber_fk,trans_detserialnumber,trans_type,pro_serialnumber,on_hand,on_sale,in_order,sold,free_for_allocation,activestatus) values('$warehouse_serialnumber_fk','$po_det_last_id',1,'$value',0,0,'$inorder_det_qty',0,0,1)";
								$s6 = mysql_query($query6) or die(mysql_error());
							}
					}else
						{							
							$query3 = "insert into po_map_sc(po_detserialnumber,sc_detserialnumber,allocated_qty,sc_type,self_sc,cre_userid_fk,
							cre_datetime,activestatus) values('$po_det_last_id','$scDetserials[$i]','$pro_qty','$sc_type','$self_sc','$VObjectP->cre_userid_fk',
							'$VObjectP->cre_datetime',1)";
							mysql_query($query3) or die(mysql_error());
					
							//Update Inorder Details For Self
							$query4 = "select in_order,free_for_allocation from stock_header where pro_serialnumber='$value' and warehouse_serialnumber_fk='$warehouse_serialnumber_fk'";
							$result4 = mysql_query($query4) or die(mysql_error());
							while ($row4 = mysql_fetch_object($result4)) 
							{
									$in_order = (int) $row4->in_order;
									$free_for_allocation = (int) $row4->free_for_allocation;
									
									$pre_inorder_qty = $in_order;
									$pre_free_allocation_qty = $free_for_allocation;
									
									$in_order = $in_order + $pro_qty;
									$free_for_allocation = $free_for_allocation + $pro_qty;
									
									$post_inorder_qty = $in_order;
									$post_free_allocation_qty = $free_for_allocation;
									$inorder_det_qty = $post_inorder_qty - $pre_inorder_qty;
									$free_allocation_det_qty = $post_free_allocation_qty - $pre_free_allocation_qty;
						
								$query5 = "update stock_header set in_order='$in_order',free_for_allocation='$free_for_allocation' where pro_serialnumber='$value' and warehouse_serialnumber_fk='$warehouse_serialnumber_fk'";
								$s1 = mysql_query($query5) or die(mysql_error());
								
								$query6 = "Insert Into stock_details(warehouse_serialnumber_fk,trans_detserialnumber,trans_type,pro_serialnumber,on_hand,on_sale,in_order,sold,free_for_allocation,activestatus) values('$warehouse_serialnumber_fk','$po_det_last_id',1,'$value',0,0,'$inorder_det_qty',0,'$free_allocation_det_qty',1)";
								$s6 = mysql_query($query6) or die(mysql_error());
							}
						}
				$i++;
            }
        }	
	 return $po_id;
	}
	
	 public function generatePoCode() {
        $res = "";
        $query = "select po_code from po_code_table order by po_code desc limit 1";
        $result = mysql_query($query) or die(mysql_error());
        if (mysql_num_rows($result)) {
            $row = mysql_fetch_array($result);
            $str = explode("PO", $row[0]);
            $num = intval($str[1]);
            $res = "PO" . ++$num;
        } else {
            $res = 'PO1001';
        }
        return $res;
    }
	
    public function editSalesConformation($VObject)
	{
		$sales_con_code = "";
		
		if ($VObject->sales_con_status != "DRAFT") 
		{		 
			if(mysql_real_escape_string($VObject->sales_type[0])=="Ex-Stock Sales" && $VObject->customer_serialnumber_fk!=0)
			{
				$sales_con_code = $this->generateScExstockCode();
				mysql_query("insert into sc_exstock_code(exstock_code) values ('$sales_con_code')") or die(mysql_error());
				
			}else if(mysql_real_escape_string($VObject->sales_type[0])=="Advanced Sales" && $VObject->customer_serialnumber_fk!=0)
			{
				$sales_con_code = $this->generateScAdvancedCode();
				mysql_query("insert into sc_advanced_code(advanced_code) values ('$sales_con_code')") or die(mysql_error());
				
			}else if(mysql_real_escape_string($VObject->sales_type[0])=="Indent Sales" && $VObject->customer_serialnumber_fk!=0)
			{
				$sales_con_code = $this->generateScIndentCode();
				mysql_query("insert into sc_indent_code(indent_code) values ('$sales_con_code')") or die(mysql_error());
			}else if($VObject->customer_serialnumber_fk==0)
			{
				$sales_con_code = $this->generateScSelfCode();
				mysql_query("insert into sc_self_code(self_code) values ('$sales_con_code')") or die(mysql_error());
			}
        }else {
            $sales_con_code = "Draft";
        }
		
		$return_mess = $sales_con_code;
		
        $query = "update sales_conformation_header set sales_con_code='$VObject->sales_con_code',customer_serialnumber_fk='$VObject->customer_serialnumber_fk',total_amount='$VObject->total_amount',total_qty='$VObject->total_qty',sc_term='$VObject->sc_term',sc_gst='$VObject->sc_gst',currency_serialnumber_fk='$VObject->currency_serialnumber_fk',current_rate='$VObject->current_rate',sales_con_status='$VObject->sales_con_status',sales_con_date='$VObject->sales_con_date',no_of_days='$VObject->no_of_days',expected_datetime='$VObject->expected_datetime',sc_remarks='$VObject->sc_remarks' where serialnumber='$VObject->serialnumber'";
        $s = mysql_query($query) or die(mysql_error());
        if ($s) 
		{
			mysql_query("update quot_det_sc set activestatus=0 where sc_serialnumber_fk='$VObject->serialnumber'") or die(mysql_error());
            mysql_query("update sales_conformation_details set activestatus=0 where sc_serialnumber_fk='$VObject->serialnumber'") or die(mysql_error());
			foreach ($VObject->quot_detserialnumber_fk as $r => $value) 
			{
               $query1 = "insert into quot_det_sc(quot_detserialnumber_fk,sc_serialnumber_fk,cre_userid_fk,cre_datetime,activestatus) values('$value','$VObject->serialnumber','$VObject->cre_userid_fk','$VObject->cre_datetime',1)";
               mysql_query($query1) or die(mysql_error());
            }
			foreach ($VObject->pro_serialnumber_fk as $r => $value) 
			{
				$pro_detserialnumber = mysql_real_escape_string($VObject->pro_detserialnumber[$r]);
				$base_currency_detserialnumber_fk = mysql_real_escape_string($VObject->base_currency_detserialnumber_fk[$r]);
                $price_per_unit = mysql_real_escape_string($VObject->price_per_unit[$r]);
                $pro_qty = mysql_real_escape_string($VObject->pro_qty[$r]);
                $pro_total_price = mysql_real_escape_string($VObject->pro_total_price[$r]);
                $pro_status = mysql_real_escape_string($VObject->pro_status[$r]);
				$delivery_loc = mysql_real_escape_string($VObject->delivery_loc[$r]);
                $sales_type = mysql_real_escape_string($VObject->sales_type[$r]);
                $warehouse_serialnumber_fk = 0;//mysql_real_escape_string($VObject->warehouse_serialnumber_fk[$r]);
                $query2 = "insert into sales_conformation_details(sc_serialnumber_fk,pro_serialnumber_fk,pro_detserialnumber,base_currency_detserialnumber_fk,pro_qty,price_per_unit,pro_total_price,pro_status,delivery_loc,warehouse_serialnumber_fk,sales_type,cre_userid_fk,cre_datetime,activestatus) values('$VObject->serialnumber','$value','$pro_detserialnumber','$base_currency_detserialnumber_fk','$pro_qty','$price_per_unit','$pro_total_price','$pro_status','$delivery_loc','$warehouse_serialnumber_fk','$sales_type','$VObject->cre_userid_fk','$VObject->cre_datetime',1)";
                mysql_query($query2) or die(mysql_error());
				$lastDetId = mysql_insert_id();
				
				if($sales_type == "Ex-Stock Sales")
				{
				  $this->mapExstockScWithPO($lastDetId,$value,$pro_qty,$VObject->cre_userid_fk,$VObject->cre_datetime);
				}else if($sales_type == "Advanced Sales")
				{
                  $this->mapAdvancedScWithPO($lastDetId,$value,$pro_qty,$VObject->cre_userid_fk,$VObject->cre_datetime);
				}
            }
        }
		
       return $return_mess;
    }

    public function deleteSalesConformation($serialnumber) {
        $query1 = ("update sales_conformation_header set activestatus=0 where serialnumber='$serialnumber'") or die(mysql_error());
        mysql_query($query1) or die(mysql_error());
		$query2 = ("update quot_det_sc set activestatus=0 where sc_serialnumber_fk='$serialnumber'") or die(mysql_error());
        mysql_query($query2) or die(mysql_error());
        $query3 = ("update sales_conformation_details set activestatus=0 where sc_serialnumber_fk='$serialnumber'") or die(mysql_error());
        mysql_query($query3) or die(mysql_error());
        return $serialnumber;
    }

	public function generateScExstockCode() {
        $res = "";
        $query = "select exstock_code from sc_exstock_code order by exstock_code desc limit 1";
        $result = mysql_query($query) or die(mysql_error());
        if (mysql_num_rows($result)) {
            $row = mysql_fetch_array($result);
            $str = explode("ESC", $row[0]);
            $num = intval($str[1]);
            $res = "ESC" . ++$num;
        } else {
            $res = 'ESC1001';
        }
        return $res;
    }
	
	 public function generateScAdvancedCode() {
        $res = "";
        $query = "select advanced_code from sc_advanced_code order by advanced_code desc limit 1";
        $result = mysql_query($query) or die(mysql_error());
        if (mysql_num_rows($result)) {
            $row = mysql_fetch_array($result);
            $str = explode("ASC", $row[0]);
            $num = intval($str[1]);
            $res = "ASC" . ++$num;
        } else {
            $res = 'ASC1001';
        }
        return $res;
    }
	
	public function generateScIndentCode() {
        $res = "";
        $query = "select indent_code from sc_indent_code order by indent_code desc limit 1";
        $result = mysql_query($query) or die(mysql_error());
        if (mysql_num_rows($result)) {
            $row = mysql_fetch_array($result);
            $str = explode("ISC", $row[0]);
            $num = intval($str[1]);
            $res = "ISC" . ++$num;
        } else {
            $res = 'ISC1001';
        }
        return $res;
    }
	
	public function generateScSelfCode() {
        $res = "";
        $query = "select self_code from sc_self_code order by self_code desc limit 1";
        $result = mysql_query($query) or die(mysql_error());
        if (mysql_num_rows($result)) {
            $row = mysql_fetch_array($result);
            $str = explode("AESC", $row[0]);
            $num = intval($str[1]);
            $res = "AESC" . ++$num;
        } else {
            $res = 'AESC1001';
        }
        return $res;
    }

	public function getFreeAllocationQty($pro_serialnumber)
	{
		$ret = array();
		
		$pro_name = '';
		$part_number = '';
		$stamp_number = '';
		
		$queryPro = "select pro_name,part_number,model_no,stamp_number from product_header 
				where serialnumber='$pro_serialnumber' and activestatus=1";
                $resPro = mysql_query($queryPro);
                while ($rowPro = mysql_fetch_object($resPro)) {
                   $pro_name = $rowPro->pro_name;
                   $part_number = $rowPro->part_number;
                   $stamp_number = $rowPro->stamp_number;
                }
		
        $query = "Select po_detserialnumber,pro_qty,po_serialnumber_fk from po_details where pro_serialnumber_fk ='$pro_serialnumber' and 
				  activestatus=1 and po_detserialnumber Not In(Select po_detserialnumber from po_map_sc where activestatus=1 and self_sc=0
				  and (Select (pd.pro_qty - SUM(pms.allocated_qty)) as bal_qty from po_details as pd,po_map_sc as pms where 
				  pd.pro_serialnumber_fk ='$pro_serialnumber' and pd.activestatus=1 and pms.activestatus=1 and pms.self_sc=0 and 
				  pd.po_detserialnumber=pms.po_detserialnumber GROUP BY pms.po_detserialnumber) <= 0)";
		$s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) 
		{
		  $temp = new VoFreeAllocated();
		  $temp->po_detserialnumber = (int) $row->po_detserialnumber;
		  $temp->pro_qty = (int) $row->pro_qty;
		  
		  $result1 = mysql_query("Select SUM(allocated_qty) as allocated from po_map_sc where activestatus=1 and 
					 po_detserialnumber='$row->po_detserialnumber' and self_sc=0") or die(mysql_error());
		 while ($row1 = mysql_fetch_object($result1)) 
		  {
		    $allocated = (int) $row1->allocated;
			$temp->free_for_allocated = ($temp->pro_qty - $allocated);
		  }
		  
		  $result2 = mysql_query("Select * from po_header where activestatus=1 and po_serialnumber='$row->po_serialnumber_fk'") or die(mysql_error());
		  while ($row2 = mysql_fetch_object($result2)) 
		  {
		    $temp->po_id = $row2->po_id;
		    $temp->po_confirm_date = date("d-m-Y", strtotime($row2->po_confirm_date));
		    $temp->expected_datetime = date("d-m-Y", strtotime($row2->expected_datetime));
		  }
		  
		  $temp->pro_serialnumber = $pro_serialnumber;
		  $temp->pro_name = $pro_name;
          $temp->part_number = $part_number;
          $temp->stamp_number = $stamp_number;
		  
		  $temp->allocated_qty = 0;
		  
		  $ret[] = $temp;
		}
	return $ret;
	}
	 public function getAllSCHeaderList() 
	{
	$ret = array();
	 $query = "select serialnumber,sales_con_code from sales_conformation_header where activestatus=1 and sales_con_status='CONFIRM'";
	$s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) 
		{
            $temp = new VoHeaderSC();
            $temp->serialnumber = (int) $row->serialnumber;
			 $temp->sales_con_code = $row->sales_con_code;
			$ret[] = $temp;
		}
		return $ret;
	}
    public function getAllSalaesConformation() 
	{
        $ret = array();
        $query = "select sh.serialnumber,sh.sales_con_code,sh.no_of_days,sh.customer_serialnumber_fk,sh.total_amount,sh.total_qty,sh.sc_term,sh.sc_gst,sh.currency_serialnumber_fk,sh.current_rate,
		sh.sales_con_status,sh.sales_con_date,sh.expected_datetime,sh.sc_remarks,sh.cre_userid_fk,sh.cre_datetime,sh.activestatus,IFNULL(ch.client_name,'Alliance Enterprices') as customer 
		from sales_conformation_header as sh LEFT JOIN client_header as ch ON ch.activestatus=1 and ch.serialnumber=sh.customer_serialnumber_fk where sh.activestatus=1 and sh.sales_con_status!='VOID'";
        $s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) 
		{
            $temp = new VoSalesConformation();
            $temp->serialnumber = (int) $row->serialnumber;
            $temp->sales_con_code = $row->sales_con_code;
            $temp->customer_serialnumber_fk = (int) $row->customer_serialnumber_fk;
            $temp->total_amount = (double) $row->total_amount;
            $temp->total_qty = (int) $row->total_qty;
			$temp->sc_term = (int) $row->sc_term;
			$temp->sc_gst = (double) $row->sc_gst;
			$temp->currency_serialnumber_fk = (int) $row->currency_serialnumber_fk;
			$temp->current_rate = (double) $row->current_rate;
            $temp->sales_con_status = $row->sales_con_status;
            $temp->sales_con_date = date("d-m-Y", strtotime($row->sales_con_date));
			$temp->no_of_days = (int) $row->no_of_days;
			$temp->expected_datetime = date("d-m-Y", strtotime($row->expected_datetime));
            $temp->sc_remarks = $row->sc_remarks;
            $temp->cre_userid_fk = (int) $row->cre_userid_fk;
            $temp->cre_datetime = $row->cre_datetime;
            $temp->activestatus = (boolean) $row->activestatus;
			$temp->client_name = $row->customer;
			
			$query1 = "select qms.quot_det_sc_serialnumber,qms.sc_serialnumber_fk,qms.quot_detserialnumber_fk,qd.quotation_serialnumber_fk, 
			qd.pro_qty,qd.pro_serialnumber_fk,qh.quotation_code from quot_det_sc as qms LEFT JOIN quotation_details as qd ON qd.detserialnumber=qms.quot_detserialnumber_fk 
			and qd.activestatus=1 LEFT JOIN quotation_header as qh ON qh.activestatus=1 and qh.serialnumber=qd.quotation_serialnumber_fk where qms.activestatus=1 and
			qms.sc_serialnumber_fk='$row->serialnumber'";
            $s1 = mysql_query($query1) or die(mysql_error());
            while ($row1 = mysql_fetch_object($s1)) {
                $temp->quot_det_sc_serialnumber[] = (int) $row1->quot_det_sc_serialnumber;
				$temp->quot_detserialnumber_fk[] = (int) $row1->quot_detserialnumber_fk;
				$temp->quotation_serialnumber_fk[] = (int) $row1->quotation_serialnumber_fk;
				$temp->quotation_code[] = $row1->quotation_code;
                $temp->quot_sc_serialnumber_fk[] = (int) $row1->sc_serialnumber_fk;
				$temp->quot_pro_serialnumber_fk[] = (int) $row1->pro_serialnumber_fk;
				$temp->quot_pro_qty[] = (int)$row1->pro_qty;
            }

	
			$query2 = "Select sd.detserialnumber,sd.sc_serialnumber_fk,sd.pro_serialnumber_fk,sd.pro_qty,sd.price_per_unit,sd.pro_total_price,sd.pro_status,sd.delivery_loc,sd.warehouse_serialnumber_fk,sd.sales_type,
			sd.base_currency_detserialnumber_fk,cd.currency_value,ph.pro_name,ph.part_number,ph.stamp_number,ph.pro_description,ph.model_no,sd.pro_detserialnumber,prd.currency_serialnumber,prd.markup_type,prd.listprice,prd.discount,
			prd.markup1,prd.markup2,prd.markup3,prd.markup4,prd.markup5,prd.markup6,prd.default_price from sales_conformation_details as sd	LEFT JOIN product_details as prd ON prd.pro_detserialnumber=sd.pro_detserialnumber 
			LEFT JOIN product_header as ph ON ph.serialnumber=sd.pro_serialnumber_fk and ph.activestatus=1 LEFT JOIN currency_detail as cd ON cd.detserialnumber=sd.base_currency_detserialnumber_fk and 
			cd.to_currency_fk=prd.currency_serialnumber where sd.sc_serialnumber_fk='$row->serialnumber' and sd.pro_qty>0 and sd.activestatus=1";
            $s2 = mysql_query($query2) or die(mysql_error());
            while ($row2 = mysql_fetch_object($s2)) 
			{
                $temp->detserialnumber[] = (int) $row2->detserialnumber;
                $temp->sc_serialnumber_fk[] = (int) $row2->sc_serialnumber_fk;
                $temp->pro_serialnumber_fk[] = (int) $row2->pro_serialnumber_fk;
				$temp->pro_detserialnumber[] = (int) $row2->pro_detserialnumber;
				$temp->base_currency_detserialnumber_fk[] = (int) $row2->base_currency_detserialnumber_fk;
                $temp->pro_qty[] = (int) $row2->pro_qty;
                if($row->customer_serialnumber_fk!=0) {
					$temp->price_per_unit[] = $row2->price_per_unit;
				}
                $temp->pro_total_price[] = (double) $row2->pro_total_price;
                $temp->pro_status[] = $row2->pro_status;
                $temp->delivery_loc[] = $row2->delivery_loc;
                $temp->warehouse_serialnumber_fk[] = (int) $row2->warehouse_serialnumber_fk;
                $temp->sales_type[] = $row2->sales_type;
				$temp->pro_name[] = $row2->pro_name;
				$temp->part_number[] = $row2->part_number;
				$temp->stamp_number[] = $row2->stamp_number;
				$temp->pro_description[] = $row2->pro_description;
				$temp->model_no[] = $row2->model_no;
				
				$m1 = (double)$row2->markup1;
				$m2 = (double)$row2->markup2;
				$m3 = (double)$row2->markup3;
				$m4 = (double)$row2->markup4;
				$m5 = (double)$row2->markup5;
				$m6 = (double)$row2->markup6;	
				
				$listprice = (double)$row2->listprice;
				$discount = (double)$row2->discount;
				
				$base_currency_value =(double)$row2->currency_value;
				
				$netprice = round(($listprice - (($discount/100)*$listprice)),2);
			
				$costPrice = $netprice * $base_currency_value;
			    $other_price = $costPrice / $temp->current_rate;
				
				if($row->customer_serialnumber_fk!=0) {
					$sgd_listprice = round((($listprice - ($listprice * $discount/100))* $base_currency_value),4);
					$temp->sgd_listprice[] = round(($sgd_listprice /  $row->current_rate),4);
				}else{$temp->sgd_listprice[] = round($row2->price_per_unit,4);}
				
				$price1=0;
				$price2=0;
				$price3=0;
				$price4=0;
				$price5=0;
				$price6=0;
				
				$priceQ1=0;
				$priceQ2=0;
				$priceQ3=0;
				$priceQ4=0;
				$priceQ5=0;
				$priceQ6=0;
			
			if($row2->markup_type!='Net Markup')
			 {
				$price1 = round(($costPrice/(1-($m1/100))),4);
				$price2 = round(($costPrice/(1-($m2/100))),4);
				$price3 = round(($costPrice/(1-($m3/100))),4);
				$price4 = round(($costPrice/(1-($m4/100))),4);
				$price5 = round(($costPrice/(1-($m5/100))),4);
				$price6 = round(($costPrice/(1-($m6/100))),4);
			 }else{
				$price1 = round(($costPrice*(1+($m1/100))),4);
				$price2 = round(($costPrice*(1+($m2/100))),4);
				$price3 = round(($costPrice*(1+($m3/100))),4);
				$price4 = round(($costPrice*(1+($m4/100))),4);
				$price5 = round(($costPrice*(1+($m5/100))),4);
				$price6 = round(($costPrice*(1+($m6/100))),4);
			 }
			 
			 $priceQ1 = round(($price1/$temp->current_rate),4);
			 $priceQ2 = round(($price2/$temp->current_rate),4);
			 $priceQ3 = round(($price3/$temp->current_rate),4);
			 $priceQ4 = round(($price4/$temp->current_rate),4);
			 $priceQ5 = round(($price5/$temp->current_rate),4);
			 $priceQ6 = round(($price6/$temp->current_rate),4);
			 
				$priceList = array();

				$priceObj1 = new VOPrice();
				$priceObj1->sgd_price = (double)$price1;
				$priceObj1->other_price = (double)$priceQ1;
				$priceObj1->hilight = 0;
				if ($row2->default_price == 'Price A') {
					$priceObj1->hilight = 1;
					if($row->customer_serialnumber_fk==0) {
						$temp->price_per_unit[] = $priceQ1;
					}
				}
				$priceList[] = $priceObj1;

				$priceObj2 = new VOPrice();
				$priceObj2->sgd_price = (double)$price2;
				$priceObj2->other_price = (double)$priceQ2;
				$priceObj2->hilight = 0;
				if ($row2->default_price == 'Price B') {
					$priceObj2->hilight = 1;
					if($row->customer_serialnumber_fk==0) {
						$temp->price_per_unit[] = $priceQ2;
					}
				}
				$priceList[] = $priceObj2;

				$priceObj3 = new VOPrice();
				$priceObj3->sgd_price = (double)$price3;
				$priceObj3->other_price = (double)$priceQ3;
				$priceObj3->hilight = 0;
				if ($row2->default_price == 'Price C') {
					$priceObj3->hilight = 1;
					if($row->customer_serialnumber_fk==0) {
						$temp->price_per_unit[] = $priceQ3;
					}
				}
				$priceList[] = $priceObj3;

				$priceObj4 = new VOPrice();
				$priceObj4->sgd_price = (double)$price4;
				$priceObj4->other_price = (double)$priceQ4;
				$priceObj4->hilight = 0;
				if ($row2->default_price == 'Price D') {
					$priceObj4->hilight = 1;
					if($row->customer_serialnumber_fk==0) {
						$temp->price_per_unit[] = $priceQ4;
					}
				}
				$priceList[] = $priceObj4;

				$priceObj5 = new VOPrice();
				$priceObj5->sgd_price = (double)$price5;
				$priceObj5->other_price = (double)$priceQ5;
				$priceObj5->hilight = 0;
				if ($row2->default_price == 'Price E') {
					$priceObj5->hilight = 1;
					if($row->customer_serialnumber_fk==0) {
						$temp->price_per_unit[] = $priceQ5;
					}
				}
				$priceList[] = $priceObj5;

				$priceObj6 = new VOPrice();
				$priceObj6->sgd_price = (double)$price6;
				$priceObj6->other_price = (double)$priceQ6;
				$priceObj6->hilight = 0;
				if ($row2->default_price == 'Price F') {
					$priceObj6->hilight = 1;
					if($row->customer_serialnumber_fk==0) {
						$temp->price_per_unit[] = $priceQ6;
					}
				}
				$priceList[] = $priceObj6;

				$temp->price_list[] = $priceList;
				$temp->default_price[] = $row2->default_price;
            }
			
			$ret[] = $temp;
			mysql_free_result($s2);
       }
       mysql_free_result($s);
       return $ret;
    }

    public function getSalesConformationByCode($serialnumber) 
	{
        $ret = array();
        $query = "select sh.serialnumber,sh.sales_con_code,sh.no_of_days,sh.customer_serialnumber_fk,sh.total_amount,sh.total_qty,sh.sc_term,sh.sc_gst,sh.currency_serialnumber_fk,sh.current_rate,
		sh.sales_con_status,sh.sales_con_date,sh.expected_datetime,sh.sc_remarks,sh.cre_userid_fk,sh.cre_datetime,sh.activestatus,IFNULL(ch.client_name,'Alliance Enterprices') as customer 
		from sales_conformation_header as sh LEFT JOIN client_header as ch ON ch.activestatus=1 and ch.serialnumber=sh.customer_serialnumber_fk where sh.activestatus=1 and sh.sales_con_status!='VOID' and sh.serialnumber='$serialnumber'";
        $s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) 
		{
            $temp = new VoSalesConformation();
            $temp->serialnumber = (int) $row->serialnumber;
            $temp->sales_con_code = $row->sales_con_code;
            $temp->customer_serialnumber_fk = (int) $row->customer_serialnumber_fk;
            $temp->total_amount = (double) $row->total_amount;
            $temp->total_qty = (int) $row->total_qty;
			$temp->sc_term = (int) $row->sc_term;
			$temp->sc_gst = (double) $row->sc_gst;
			$temp->currency_serialnumber_fk = (int) $row->currency_serialnumber_fk;
			$temp->current_rate = (double) $row->current_rate;
            $temp->sales_con_status = $row->sales_con_status;
            $temp->sales_con_date = date("d-m-Y", strtotime($row->sales_con_date));
			$temp->no_of_days = (int) $row->no_of_days;
			$temp->expected_datetime = date("d-m-Y", strtotime($row->expected_datetime));
            $temp->sc_remarks = $row->sc_remarks;
            $temp->cre_userid_fk = (int) $row->cre_userid_fk;
            $temp->cre_datetime = $row->cre_datetime;
            $temp->activestatus = (boolean) $row->activestatus;
			$temp->client_name = $row->customer;
			
			$query1 = "select qms.quot_det_sc_serialnumber,qms.sc_serialnumber_fk,qms.quot_detserialnumber_fk,qd.quotation_serialnumber_fk, 
			qd.pro_qty,qd.pro_serialnumber_fk,qh.quotation_code from quot_det_sc as qms LEFT JOIN quotation_details as qd ON qd.detserialnumber=qms.quot_detserialnumber_fk 
			and qd.activestatus=1 LEFT JOIN quotation_header as qh ON qh.activestatus=1 and qh.serialnumber=qd.quotation_serialnumber_fk where qms.activestatus=1 and
			qms.sc_serialnumber_fk='$row->serialnumber'";
            $s1 = mysql_query($query1) or die(mysql_error());
            while ($row1 = mysql_fetch_object($s1)) {
                $temp->quot_det_sc_serialnumber[] = (int) $row1->quot_det_sc_serialnumber;
				$temp->quot_detserialnumber_fk[] = (int) $row1->quot_detserialnumber_fk;
				$temp->quotation_serialnumber_fk[] = (int) $row1->quotation_serialnumber_fk;
				$temp->quotation_code[] = $row1->quotation_code;
                $temp->quot_sc_serialnumber_fk[] = (int) $row1->sc_serialnumber_fk;
				$temp->quot_pro_serialnumber_fk[] = (int) $row1->pro_serialnumber_fk;
				$temp->quot_pro_qty[] = (int)$row1->pro_qty;
            }

	
			$query2 = "Select sd.detserialnumber,sd.sc_serialnumber_fk,sd.pro_serialnumber_fk,sd.pro_qty,sd.price_per_unit,sd.pro_total_price,sd.pro_status,sd.delivery_loc,sd.warehouse_serialnumber_fk,sd.sales_type,
			sd.base_currency_detserialnumber_fk,cd.currency_value,ph.pro_name,ph.part_number,ph.stamp_number,ph.pro_description,ph.model_no,sd.pro_detserialnumber,prd.currency_serialnumber,prd.markup_type,prd.listprice,prd.discount,
			prd.markup1,prd.markup2,prd.markup3,prd.markup4,prd.markup5,prd.markup6,prd.default_price from sales_conformation_details as sd	LEFT JOIN product_details as prd ON prd.pro_detserialnumber=sd.pro_detserialnumber 
			LEFT JOIN product_header as ph ON ph.serialnumber=sd.pro_serialnumber_fk and ph.activestatus=1 LEFT JOIN currency_detail as cd ON cd.detserialnumber=sd.base_currency_detserialnumber_fk and 
			cd.to_currency_fk=prd.currency_serialnumber where sd.sc_serialnumber_fk='$row->serialnumber' and sd.pro_qty>0 and sd.activestatus=1";
            $s2 = mysql_query($query2) or die(mysql_error());
            while ($row2 = mysql_fetch_object($s2)) 
			{
                $temp->detserialnumber[] = (int) $row2->detserialnumber;
                $temp->sc_serialnumber_fk[] = (int) $row2->sc_serialnumber_fk;
                $temp->pro_serialnumber_fk[] = (int) $row2->pro_serialnumber_fk;
				$temp->pro_detserialnumber[] = (int) $row2->pro_detserialnumber;
				$temp->base_currency_detserialnumber_fk[] = (int) $row2->base_currency_detserialnumber_fk;
                $temp->pro_qty[] = (int) $row2->pro_qty;
				if($row->customer_serialnumber_fk!=0) {
					$temp->price_per_unit[] = $row2->price_per_unit;
				}
                $temp->pro_total_price[] = (double) $row2->pro_total_price;
                $temp->pro_status[] = $row2->pro_status;
                $temp->delivery_loc[] = $row2->delivery_loc;
                $temp->warehouse_serialnumber_fk[] = (int) $row2->warehouse_serialnumber_fk;
                $temp->sales_type[] = $row2->sales_type;
				$temp->pro_name[] = $row2->pro_name;
				$temp->part_number[] = $row2->part_number;
				$temp->stamp_number[] = $row2->stamp_number;
				$temp->pro_description[] = $row2->pro_description;
				$temp->model_no[] = $row2->model_no;
				
				$m1 = (double)$row2->markup1;
				$m2 = (double)$row2->markup2;
				$m3 = (double)$row2->markup3;
				$m4 = (double)$row2->markup4;
				$m5 = (double)$row2->markup5;
				$m6 = (double)$row2->markup6;	
				
				$listprice = (double)$row2->listprice;
				$discount = (double)$row2->discount;
				
				$base_currency_value =(double)$row2->currency_value;
				
				$netprice = round(($listprice - (($discount/100)*$listprice)),2);
			
				$costPrice = $netprice * $base_currency_value;
			    $other_price = $costPrice / $temp->current_rate;
				
				if($row->customer_serialnumber_fk!=0) {
					$sgd_listprice = round((($listprice - ($listprice * $discount/100))* $base_currency_value),4);
					$temp->sgd_listprice[] = round(($sgd_listprice /  $row->current_rate),4);
				}else{$temp->sgd_listprice[] = round($row2->price_per_unit,4);}
				
				$price1=0;
				$price2=0;
				$price3=0;
				$price4=0;
				$price5=0;
				$price6=0;
				
				$priceQ1=0;
				$priceQ2=0;
				$priceQ3=0;
				$priceQ4=0;
				$priceQ5=0;
				$priceQ6=0;
			
			if($row2->markup_type!='Net Markup')
			 {
				$price1 = round(($costPrice/(1-($m1/100))),4);
				$price2 = round(($costPrice/(1-($m2/100))),4);
				$price3 = round(($costPrice/(1-($m3/100))),4);
				$price4 = round(($costPrice/(1-($m4/100))),4);
				$price5 = round(($costPrice/(1-($m5/100))),4);
				$price6 = round(($costPrice/(1-($m6/100))),4);
			 }else{
				$price1 = round(($costPrice*(1+($m1/100))),4);
				$price2 = round(($costPrice*(1+($m2/100))),4);
				$price3 = round(($costPrice*(1+($m3/100))),4);
				$price4 = round(($costPrice*(1+($m4/100))),4);
				$price5 = round(($costPrice*(1+($m5/100))),4);
				$price6 = round(($costPrice*(1+($m6/100))),4);
			 }
			 
			 $priceQ1 = round(($price1/$temp->current_rate),4);
			 $priceQ2 = round(($price2/$temp->current_rate),4);
			 $priceQ3 = round(($price3/$temp->current_rate),4);
			 $priceQ4 = round(($price4/$temp->current_rate),4);
			 $priceQ5 = round(($price5/$temp->current_rate),4);
			 $priceQ6 = round(($price6/$temp->current_rate),4);
			 
				$priceList = array();

				$priceObj1 = new VOPrice();
				$priceObj1->sgd_price = (double)$price1;
				$priceObj1->other_price = (double)$priceQ1;
				$priceObj1->hilight = 0;
				if ($row2->default_price == 'Price A') {
					$priceObj1->hilight = 1;
					if($row->customer_serialnumber_fk==0) {
						$temp->price_per_unit[] = $priceQ1;
					}
				}
				$priceList[] = $priceObj1;

				$priceObj2 = new VOPrice();
				$priceObj2->sgd_price = (double)$price2;
				$priceObj2->other_price = (double)$priceQ2;
				$priceObj2->hilight = 0;
				if ($row2->default_price == 'Price B') {
					$priceObj2->hilight = 1;
					if($row->customer_serialnumber_fk==0) {
						$temp->price_per_unit[] = $priceQ2;
					}
				}
				$priceList[] = $priceObj2;

				$priceObj3 = new VOPrice();
				$priceObj3->sgd_price = (double)$price3;
				$priceObj3->other_price = (double)$priceQ3;
				$priceObj3->hilight = 0;
				if ($row2->default_price == 'Price C') {
					$priceObj3->hilight = 1;
					if($row->customer_serialnumber_fk==0) {
						$temp->price_per_unit[] = $priceQ3;
					}
				}
				$priceList[] = $priceObj3;

				$priceObj4 = new VOPrice();
				$priceObj4->sgd_price = (double)$price4;
				$priceObj4->other_price = (double)$priceQ4;
				$priceObj4->hilight = 0;
				if ($row2->default_price == 'Price D') {
					$priceObj4->hilight = 1;
					if($row->customer_serialnumber_fk==0) {
						$temp->price_per_unit[] = $priceQ4;
					}
				}
				$priceList[] = $priceObj4;

				$priceObj5 = new VOPrice();
				$priceObj5->sgd_price = (double)$price5;
				$priceObj5->other_price = (double)$priceQ5;
				$priceObj5->hilight = 0;
				if ($row2->default_price == 'Price E') {
					$priceObj5->hilight = 1;
					if($row->customer_serialnumber_fk==0) {
						$temp->price_per_unit[] = $priceQ5;
					}
				}
				$priceList[] = $priceObj5;

				$priceObj6 = new VOPrice();
				$priceObj6->sgd_price = (double)$price6;
				$priceObj6->other_price = (double)$priceQ6;
				$priceObj6->hilight = 0;
				if ($row2->default_price == 'Price F') {
					$priceObj6->hilight = 1;
					if($row->customer_serialnumber_fk==0) {
						$temp->price_per_unit[] = $priceQ6;
					}
				}
				$priceList[] = $priceObj6;

				$temp->price_list[] = $priceList;
				$temp->default_price[] = $row2->default_price;
            }
			
			$ret[] = $temp;
			mysql_free_result($s2);
       }
      mysql_free_result($s);
      return $ret;
    }

    public function getAllStockOnSc() {
        $ret = array();
        $query = "select pro_serialnumber,Sum(in_order) as in_order,Sum(on_hand) as on_hand,Sum(on_sale) as on_sale,
				  Sum(sold) as sold,Sum(free_for_allocation) as free_for_allocation from stock_header where activestatus=1 GROUP BY pro_serialnumber";
        $result = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($result)) {
            $temp = new VoStock();
            $temp->pro_serialnumber = (int) $row->pro_serialnumber;
            $temp->in_order = (int) $row->in_order;
            $temp->on_hand = (int) $row->on_hand;
            $temp->on_sale = (int) $row->on_sale;
            $temp->sold = (int) $row->sold;
			$temp->free_for_allocation = (int) $row->free_for_allocation;
            $temp->warehouse_serialnumber_fk = 0;//(int) $row->warehouse_serialnumber_fk;
            $ret[] = $temp;
        }
        return $ret;
    }
}

class VoSalesConformation {
	var $serialnumber;	  
	var $quot_det_sc_serialnumber;
    var $quot_detserialnumber_fk;
	var $quotation_serialnumber_fk;
	var $quotation_code;
	var $quot_sc_serialnumber_fk;
	var $quot_pro_serialnumber_fk;
	var $quot_pro_qty;
    var $customer_serialnumber_fk;
    var $client_name;
    var $total_amount;
    var $total_qty;
	var $sc_term;
	var $sc_gst;
	var $currency_serialnumber_fk;
	var $current_rate;
    var $sales_con_status;
    var $sales_con_date;
	var $no_of_days;
	var $expected_datetime;
    var $sc_remarks;
    var $detserialnumber;
    var $sc_serialnumber_fk;
    var $pro_serialnumber_fk;
	var $pro_detserialnumber;
	var $base_currency_detserialnumber_fk;
	var $pro_name;
	var $pro_description;
    var $part_number;
	var $stamp_number;
    var $model_no;
	var $default_price;
	var $sgd_listprice;
	var $price_list;
    var $pro_qty;
    var $price_per_unit;
    var $pro_total_price;
    var $pro_status;
    var $delivery_loc;
    var $warehouse_serialnumber_fk;
    var $sales_type;
    var $cre_userid_fk;
    var $cre_datetime;
    var $activestatus;
}

class VoPurchaseOrder {
	var $po_serialnumber;
	var $po_id;
    var $supplier_serialnumber_fk; 
	var $total_amount;
	var $total_qty;
	var $po_term;
    var $po_gst;
    var $currency_serialnumber_fk;
    var $current_rate;
    var $po_issue_date;
    var $po_confirm_date;
	var $no_of_days;
    var $expected_datetime;
	var $po_status;
    var $po_remarks;
    var $enq_det_po_serialnumber;
	var $enq_code;
	var $enq_serialnumber_fk;
	var $enq_pro_serialnumber_fk;
	var	$enq_detserialnumber_fk;
	var $enq_pro_qty;
    var $po_serialnumber_fk;
    var $po_detserialnumber;
	var $sales_con_code;
	var $self_sc;
	var $sc_detserialnumber_fk;
    var $pro_serialnumber_fk;
	var $pro_name;
	var $part_number;
	var $pro_description;
    var $pro_qty;
    var $unit_price;
    var $pro_total_price;
    var $pro_status;
    var $warehouse_serialnumber_fk;
	var $warehouse_name;
    var $cre_userid_fk;
    var $cre_datetime;
    var $activestatus;
}

class VoFreeAllocated{
	var $pro_serialnumber;
	var $po_detserialnumber;
	var $pro_qty;
	var $free_for_allocated;
	var $po_id;
	var $po_confirm_date;
	var $expected_datetime;
	var $allocated_qty;
}

class VoStock {
    var $pro_serialnumber;
    var $on_hand;
    var $on_sale;
    var $in_order;
    var $sold;
	var $free_for_allocation;
    var $warehouse_serialnumber_fk;
}
    
class VOPrice {
    var $sgd_price;
	var $other_price;
    var $hilight;
}

class VoCancelledSc{
	var $serialnumber;
	var $sc_detserialnumber_fk;
	var $cancelled_qty;
	var $cre_userid_fk;
    var $cre_datetime;
    var $activestatus;
}

class VoScSatisfied {
	var $po_detserialnumber;
    var $sc_serialnumber;
    var $sales_con_code;
	var $sc_detserialnumber;
    var $pro_serialnumber_fk;
	var $pro_name;
	var $part_number;
	var $pro_description;	
	var $sc_qty;
	var $reserved_qty;
    var $isSelected;
}
class VoHeaderSC{
	var $serialnumber;
	var $sales_con_code;
}
// $o = new SalesConformationMaster();
// $o->updateSelfScAndProductStatus(1,10,1,1);
?>